<?php
if(isset($_POST['action']))
{
    $db = new Database;
    // User Info
        $auth = Auth::getAuth('current_user');
        $user_id = $auth['id'];
        $company_id =  $auth['company_id'];
        $user_group = $auth['role'];
        $user_groupType = $auth['role_type'];
        
    $page = $_POST['page'];
    $cur_page = $page;
    $page -= 1;
    $per_page = 20; // Per page
    $previous_btn = true;
    $next_btn = true;
    $first_btn = true;
    $last_btn = true;
    $start = $page * $per_page;
    $action = $_POST['action'];
    // For Sorting
        $dataSortValue = $_POST['dataSortValue'];
        if(!empty($dataSortValue)){
            if($_POST['counterSort']%2==0){
                $sort = "ORDER BY r.date DESC";
            }else{
                $sort = "ORDER BY r.date ASC";
            }
        }else{
            $sort = "ORDER BY form_title";
        }
    if($action=="MyRequest")
    {
        $count = $db->query("SELECT * FROM tbrequest r
				    LEFT JOIN tbrequest_process rp on r.id=rp.request_id
				    LEFT JOIN tbform f on f.id=r.form_id
				    LEFT JOIN tbset_field sf on sf.companyID=f.company_id
				    WHERE r.user_id = '".$auth['id']."'
				    AND r.status != 0 AND r.status != 4 AND (rp.status=2 OR rp.status=3) and r.company_id = $company_id $sort","numrows");
	$queryRequest = $db->query("SELECT r.status as request_status, rp.approver as approver,
				  r.date as date_request,r.user_id as user_id,
				  f.id as form_id,rp.wf_master_id as wf_master_id,
				  f.form_title as form_title, r.data as data,
				  sf.colValue_formID as colValue_formID,
				  sf.numCol as numCol,
				  r.id as request_id,rp.id as requestProcess_id FROM tbrequest r
				  LEFT JOIN tbrequest_process rp on r.id=rp.request_id
				  LEFT JOIN tbform f on f.id=r.form_id
				  LEFT JOIN tbset_field sf on sf.companyID=f.company_id
				  WHERE r.user_id = '".$auth['id']."' AND r.status != 0 AND r.status != 4 AND (rp.status=2 OR rp.status=3) and r.company_id = $company_id $sort LIMIT $start, $per_page","array");
        $no_of_paginations = ceil($count / $per_page);
    }elseif($_POST['action']=="search_request"){
	$value = $_POST['value'];
	$count = $db->query("SELECT * FROM tbrequest r
				    LEFT JOIN tbrequest_process rp on r.id=rp.request_id
				    LEFT JOIN tbform f on f.id=r.form_id
				    LEFT JOIN tbset_field sf on sf.companyID=f.company_id
				    WHERE r.user_id = '".$auth['id']."'
				    AND r.status != 0 AND r.status != 4 AND (rp.status=2 OR rp.status=3)
				    AND f.form_title LIKE '%$value%'
				    
				    OR (r.user_id='".getUserID($value)."')
				    AND r.user_id = '".$auth['id']."'
				    AND r.status != 0 AND r.status != 4 AND (rp.status=2 OR rp.status=3) and r.company_id = $company_id","numrows");
	$queryRequest = $db->query("SELECT r.status as request_status, rp.approver as approver,
				  r.date as date_request,r.user_id as user_id,
				  f.id as form_id,rp.wf_master_id as wf_master_id,
				  f.form_title as form_title, r.data as data,
				  sf.colValue_formID as colValue_formID,
				  sf.numCol as numCol,
				  r.id as request_id,rp.id as requestProcess_id FROM tbrequest r
				  LEFT JOIN tbrequest_process rp on r.id=rp.request_id
				  LEFT JOIN tbform f on f.id=r.form_id
				  LEFT JOIN tbset_field sf on sf.companyID=f.company_id
				  WHERE r.user_id = '".$auth['id']."' AND r.status != 0 AND r.status != 4 AND (rp.status=2 OR rp.status=3)
				AND f.form_title LIKE '%$value%'
				
				OR (r.user_id='".getUserID($value)."')
				AND r.user_id = '".$auth['id']."' AND r.status != 0 AND r.status != 4 AND (rp.status=2 OR rp.status=3) and r.company_id = $company_id
				LIMIT $start, $per_page","array");
        $no_of_paginations = ceil($count / $per_page);
    }elseif($_POST['action']=="searchDate"){
	$value = $_POST['value'];
        $split = explode(",", $value);
	$count = $db->query("SELECT * FROM tbrequest r
				    LEFT JOIN tbrequest_process rp on r.id=rp.request_id
				    LEFT JOIN tbform f on f.id=r.form_id
				    LEFT JOIN tbset_field sf on sf.companyID=f.company_id
				    WHERE r.user_id = '".$auth['id']."'
				    AND r.status != 0 AND r.status != 4 AND (rp.status=2 OR rp.status=3) AND r.date>='$split[0]' AND r.date<='$split[1]' and r.company_id = $company_id","numrows");
	$queryRequest = $db->query("SELECT r.status as request_status, rp.approver as approver,
				  r.date as date_request,r.user_id as user_id,
				  f.id as form_id,rp.wf_master_id as wf_master_id,
				  f.form_title as form_title, r.data as data,
				  sf.colValue_formID as colValue_formID,
				  sf.numCol as numCol,
				  r.id as request_id,rp.id as requestProcess_id FROM tbrequest r
				  LEFT JOIN tbrequest_process rp on r.id=rp.request_id
				  LEFT JOIN tbform f on f.id=r.form_id
				  LEFT JOIN tbset_field sf on sf.companyID=f.company_id
				  WHERE r.user_id = '".$auth['id']."' AND r.status != 0 AND r.status != 4 AND (rp.status=2 OR rp.status=3) AND r.date>='$split[0]' AND r.date<='$split[1]' and r.company_id = $company_id
				  LIMIT $start, $per_page","array");
        $no_of_paginations = ceil($count / $per_page);
    }
    $finaldata.= '<table class="table_data myRequest" style="">
                        <thead>
                            <tr id="sortMyRequest" data-location="tblMyRequest" data-search="form_title" data-tag-action="MyRequest" p="'.$page.'" data-element="#myRequest_tbl">
				<th>#</th>
				<th>Form Title</th>
                                <th>Processor</th>';
                                // Get created/ setted form field from the database
                                $field = $db->query("SELECT * FROM tbset_field WHERE companyID='$company_id'","array");
                                $getCreatedfield = $db->query("SELECT * FROM tbset_field WHERE companyID='$company_id'","row");
                                $getCreatedfieldRows = $db->query("SELECT * FROM tbset_field WHERE companyID='$company_id'","numrows");
                                if($getCreatedfieldRows>0){
                                    foreach($field as $dataField){
                                        $HeaderValue = explode(",", $dataField['colTitle']);
                                        $split_getCreatedfield = explode(",", $dataField['colValue_formID']);
                                    }
                                    
                                    $header = array();
                                    $headerSearch = array();
                                    $headerID = array();
                                    $headerValArraySort = array();
                                    ksort($split_getCreatedfield);
                                    foreach($split_getCreatedfield as $k => $v){
                                        $split_getCreatedfield_2 = explode("->", $split_getCreatedfield[$k]);
                                        $HeaderValue_2 = explode("->",$HeaderValue[$k]);
                                        $headerValArraySort[$split_getCreatedfield_2[1]] = $split_getCreatedfield_2[0];
                                        $headerID[$split_getCreatedfield_2[1]] = $split_getCreatedfield_2[1];
                                        $headerValArraySort_2[$split_getCreatedfield_2[1]] = $HeaderValue_2[1];
                                    }
                                    ksort($headerValArraySort_2);
                                        foreach($headerValArraySort_2 as $a => $b){
                                            $finaldata.= "<th width='100'>";
                                                $finaldata.= $b;
                                            $finaldata.= "</th>";
                                        }
                                }
                                    $finaldata.= '<th>Date</th>';
				    $finaldata.= '<th>Status</th>';
                                    $finaldata.= '<th>Actions</th>';
                                
    $finaldata.= '          </tr>
                        </thead>
                        <tbody >';
    $counter = 1;
    if($count>0){
        foreach($queryRequest as $dataRequest){
	    $finaldata.='	
		<tr id="myRequestRow_'.$dataRequest['request_id'].'" class="myRequest_tbl">
		    <td><center>'.$counter++.'</center></td>
		    <td style="text-align:center">'.$dataRequest['form_title'].'</td>
		    <td style="text-align:center">'.setApproverTitle($dataRequest['approver']).'</td>';
			if($getCreatedfieldRows>0){
                            $split_getCreatedfield = explode(",", $dataRequest['colValue_formID']);
                            $fieldsWithValue = explode("~",$dataRequest['data']);
                            
                            
                            $headValue = array();
                                ksort($headerID);
                                ksort($headerValArraySort_2);
                                    foreach($fieldsWithValue as $kv => $headVal){
                                        $fieldsWithValue_2 = explode("->", $fieldsWithValue[$kv]);
                                        if(array_search($fieldsWithValue_2[0], $headerID)==true){
                                            $headValue[$fieldsWithValue_2[0]] = $fieldsWithValue_2[1];
                                        }
                                        
                                    }
                                    ksort($headValue);
                                foreach($headerID as $keyHeadVal => $valHeadKey){
                                    $finaldata.= "<td>";    
                                        $finaldata.= $headValue[$keyHeadVal];
                                    $finaldata.= "</td>";
                                    
                                }
                        }
	    $finaldata.='		
		    <td style="text-align:center">'.$dataRequest['date_request'].'</td>
		    <td style="text-align:center">'.requestStatus($dataRequest['request_status']).'</td>
		    <td style="text-align:center">
			<a class="icon-file viewUserForm pointer icon-file" style="color:#6D84B4;font-size:10px" id="viewUserForm_'.$dataRequest['request_id'].'_'.$dataRequest['requestProcess_id'].'" button-properies="none"></a>
			<a class="icon-remove actionUserRequest" rel="remove" id="remove_'.$dataRequest['request_id']."_".$dataRequest['requestProcess_id'].'" title="Remove Request" style="cursor: pointer"></a>
		    </td>
		</tr>';
	}
        //pagination
        pagination($cur_page,$page,$per_page,$start,$previous_btn,$next_btn,$first_btn,$last_btn,$no_of_paginations,$finaldata,$count,$action,$value);
    }else{
        $numCols = $getCreatedfield['numCol'] + 5;
        $finaldata.='<tr><td colspan="'.$numCols.'"><center>No Record Found</center></td></tr>';
        echo $finaldata;
    }
    
}
?>
